﻿using System;
using System.Collections.Generic;
using System.Linq;
using Dapper;
using zhangmenren.Db;
using zhangmenren.Models;

namespace zhangmenren.Repository
{
    public class UserRepository: BaseRepository
    {
        public IEnumerable<UserEntity> Select(int page=1,int lim=50,string key="")
        {
            string where = "";
            if (!string.IsNullOrEmpty(key))
            {
                where = " WHERE UserName LIKE '%" + key + "%' OR Mobile LIKE '%" + key + "%'";
            }


            string sql = @"SELECT ID,Mobile,UserName,Head,Signature,Introduction FROM [User]"+ where;
            return Execute(connection => connection.Query<UserEntity>(sql)).Skip((page - 1) * lim).Take(lim);  //Items = items
        }

        public int Add(UserEntity entity)
        {
            string sql = @"INSERT INTO [User] 
                            (Mobile
                            ,Pwd
                            ,UserName
                            ,AddDT)
                        VALUES
                            (@Mobile
                            ,@Pwd
                            ,@UserName
                            ,GETDATE())";
            return Execute(connection => connection.Execute(sql, entity));
        }

        public int AddWeXin(UserEntity entity)
        {
            string sql = @"INSERT INTO [User] 
                            ([OpenId]
                            ,[Head]
                            ,UserName
                            ,AddDT)
                        VALUES
                            (@OpenId
                            ,@Head
                            ,@UserName
                            ,GETDATE())";
            return Execute(connection => connection.Execute(sql, entity));
        }

        /// <summary>
        /// 查询作品作者，带掌门号的用户
        /// </summary>
        /// <returns>The find.</returns>
        public IEnumerable<UserEntity> Find()
        {
            string sql = @"SELECT Id,UserName FROM [User] WHERE ZMRID IS NOT NULL AND ZMRID <> ''";
            return Execute(connection => connection.Query<UserEntity>(sql));  //Items = items.Skip((pageIndex - 1) * pageSize).Take(pageSize)
        }

        /// <summary>
        ///
        /// </summary>
        /// <returns>The find.</returns>
        public IEnumerable<UserEntity> FindByZMRID(int zmrid)
        {
            string sql = @"SELECT * FROM [User] WHERE ZMRID =@ZMRID ''";
            return Execute(connection => connection.Query<UserEntity>(sql,new { ZMRID = zmrid }));  //Items = items.Skip((pageIndex - 1) * pageSize).Take(pageSize)
        }

        /// <summary>
        /// 验证用户名是否存在
        /// </summary>
        /// <returns>The user name.</returns>
        public IEnumerable<UserEntity> FindUserName(string username)
        {
            string sql = @"SELECT Id,UserName,Pwd,Head FROM [User] WHERE UserName = @UserName";
            return Execute(connection => connection.Query<UserEntity>(sql,new { UserName = username }));  //Items = items.Skip((pageIndex - 1) * pageSize).Take(pageSize)
        }


        public IEnumerable<UserEntity> FindMobile(string mobile)
        {
            string sql = @"SELECT Id,UserName,Pwd,Head FROM [User] WHERE Mobile = @Mobile";
            return Execute(connection => connection.Query<UserEntity>(sql, new { Mobile = mobile }));  //Items = items.Skip((pageIndex - 1) * pageSize).Take(pageSize)
        }

        public IEnumerable<UserEntity> FindOpenId(string openId)
        {
            string sql = @"SELECT Id,UserName,Pwd,Head FROM [User] WHERE OpenId = @OpenId";
            return Execute(connection => connection.Query<UserEntity>(sql, new { OpenId = openId }));  //Items = items.Skip((pageIndex - 1) * pageSize).Take(pageSize)
        }

		public IEnumerable<UserEntity> FindByUserId(int id)
		{
			string sql = @"SELECT * FROM [User] WHERE Id = @Id";
			return Execute(connection => connection.Query<UserEntity>(sql, new { Id = id }));  //Items = items.Skip((pageIndex - 1) * pageSize).Take(pageSize)
		}

		public IEnumerable<UserEntity> FindqqId(string qqId)
        {
            string sql = @"SELECT Id,UserName,Pwd,Head FROM [User] WHERE QQId = @QQId";
            return Execute(connection => connection.Query<UserEntity>(sql, new { QQId = qqId }));  //Items = items.Skip((pageIndex - 1) * pageSize).Take(pageSize)
        }

        public IEnumerable<UserEntity> SelectTuiJian()
        {
            string sql = @"SELECT * FROM [User] WHERE IsTuiJian = 1";
            return Execute(connection => connection.Query<UserEntity>(sql));  //Items = items.Skip((pageIndex - 1) * pageSize).Take(pageSize)
        }

        public int SetIsTuiJian( int id,DateTime date)
        {
            string sql = @"UPDATE [User] SET IsTuiJian = 0 WHERE IsTuiJian = 1 ; UPDATE [User] SET IsTuiJian = 1,ZMDT=@ZMDT WHERE Id = @Id";
            return Execute(connection => connection.Execute(sql, new { Id = id, ZMDT=date }));  //Items = items.Skip((pageIndex - 1) * pageSize).Take(pageSize)
        }

        #region 修改用户信息使用

        public int SetPwd(string pwd,string mobile)
        {
            string sql = @"UPDATE [User] SET Pwd = @Pwd WHERE Mobile = @Mobile";
            return Execute(connection => connection.Execute(sql, new { Mobile = mobile,Pwd=pwd }));  //Items = items.Skip((pageIndex - 1) * pageSize).Take(pageSize)
        }
		public int SetPwd(string pwd, int id)
		{
			string sql = @"UPDATE [User] SET Pwd = @Pwd WHERE Id = @Id";
			return Execute(connection => connection.Execute(sql, new { Id = id, Pwd = pwd }));  //Items = items.Skip((pageIndex - 1) * pageSize).Take(pageSize)
		}

		public int SetMobile(string mobile, int id)
		{
			string sql = @"UPDATE [User] SET Mobile = @Mobile WHERE Id = @Id";
			return Execute(connection => connection.Execute(sql, new { Mobile = mobile, Id = id }));  //Items = items.Skip((pageIndex - 1) * pageSize).Take(pageSize)
		}
		public int SetHead(string head, int id)
		{
			string sql = @"UPDATE [User] SET Head = @Head WHERE Id = @Id";
			return Execute(connection => connection.Execute(sql, new { Head = head, Id = id }));  //Items = items.Skip((pageIndex - 1) * pageSize).Take(pageSize)
		}
		public int SetMiaoShu(string miaoshu, int id)
		{
			string sql = @"UPDATE [User] SET Signature = @Signature WHERE Id = @Id";
			return Execute(connection => connection.Execute(sql, new { Signature = miaoshu, Id = id }));  //Items = items.Skip((pageIndex - 1) * pageSize).Take(pageSize)
		}

        public int SetJianJie(string jianjie, int id)
        {
            string sql = @"UPDATE [User] SET Introduction = @Introduction WHERE Id = @Id";
            return Execute(connection => connection.Execute(sql, new { Introduction = jianjie, Id = id }));  //Items = items.Skip((pageIndex - 1) * pageSize).Take(pageSize)
        }

        public int SetAddress(string address, int id)
		{
			string sql = @"UPDATE [User] SET Address = @Address WHERE Id = @Id";
			return Execute(connection => connection.Execute(sql, new { Address = address, Id = id }));  //Items = items.Skip((pageIndex - 1) * pageSize).Take(pageSize)
		}

		public int SetUserName(string username, int id)
		{
			string sql = @"UPDATE [User] SET UserName = @UserName WHERE Id = @Id";
			return Execute(connection => connection.Execute(sql, new { UserName = username, Id = id }));  //Items = items.Skip((pageIndex - 1) * pageSize).Take(pageSize)
		}
        public int SetZMRID(int zmrid, int id)
        {
            string sql = @"UPDATE [User] SET ZMRID = @ZMRID WHERE Id = @Id";
            return Execute(connection => connection.Execute(sql, new { ZMRID = zmrid, Id = id }));  //Items = items.Skip((pageIndex - 1) * pageSize).Take(pageSize)
        }



        #endregion
    }
}
